package com.springboot.springtest.Util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

/**
 * @author wwj
 * @date 2021/3/12 0012 17:34
 */
public class ExcelUtil {
    String PATH="D:\\";

    //导出
    public void WriteExcel03() throws IOException {
        //创建工作部
        Workbook workbook = new HSSFWorkbook();
        //工作表
        Sheet sheet = workbook.createSheet("xxx统计表");
        //创建一行
        Row row1 = sheet.createRow(0);
        //创建一个单元格(也就是列)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日观众1");
        //创建一个单元格(也就是列)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("今日观众2");
        //创建二行
        Row row2 = sheet.createRow(1);
        //创建二个单元格(也就是列)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("今日观众1");
        //创建一个单元格(也就是列)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("今日观众2");

        //生成表03版本
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试03.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("完成");
    }

    public void WriteExcel07() throws IOException {
        //创建工作部
        Workbook workbook = new XSSFWorkbook();
        //工作表
        Sheet sheet = workbook.createSheet("xxx统计表");
        //创建一行
        Row row1 = sheet.createRow(0);
        //创建一个单元格(也就是列)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日观众1");
        //创建一个单元格(也就是列)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("今日观众2");
        //创建二行
        Row row2 = sheet.createRow(1);
        //创建二个单元格(也就是列)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("今日观众1");
        //创建一个单元格(也就是列)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("今日观众2");

        //生成表07版本
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试07.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("完成");
    }
    //导入
    public void ReadExcel03() throws IOException {
        String path="D:\\";
        //获取文件流
        FileInputStream inputStream = new FileInputStream(path+"测试03.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        //读取工作簿
        Sheet sheet = workbook.getSheetAt(0);
        //得到行
        Row rows = sheet.getRow(0);
        //判断每列类型
        if (rows!=null){
            //列总
            int cellCount = rows.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount;cellNum++){
                Cell cell=rows.getCell(cellNum);
                if (cell!=null){
                    int cellType=cell.getCellType();
                    //第一行的每个值
                    String cellValue=cell.getStringCellValue();
                    System.out.println(cellValue+" | ");
                }
            }
        }
        //行总
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1 ; rowNum < rowCount;rowNum++){
            Row rowData = sheet.getRow(rowNum);
            if (rowData!=null){
                //读取列
                int cellCount=rows.getPhysicalNumberOfCells();
                for (int cellNum = 0 ; cellNum<cellCount;cellNum++){
                    Cell cell = rowData.getCell(cellNum);
                    if (cell!=null){
                        int cellType=cell.getCellType();
                        String cellValue="";
                        switch (cellType){
                            case HSSFCell.CELL_TYPE_STRING:   //字符串类型
                                cellValue=cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: //布尔类型
                                cellValue=String.valueOf(cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: //空
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字类型
                                if (HSSFDateUtil.isCellDateFormatted(cell))
                                { //是日期的话
                                    Date date = cell.getDateCellValue();
                                    //   cellValue= new DateTime().toString("yyy-MM-dd");  引入工具类后可以解开注释
                                }
                                else {
                                    //数字转字符转
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue=cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: //数据类型错误
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        //关闭流
        inputStream.close();


    }
}
